-- exec sp_shipped_quarter_report 1
ALTER              proc sp_shipped_quarter_report

@order_type int
as		
begin

	declare @results Table(ProductID int, COOlQty int, COOLUnitCost float, COOLReqDate datetime, COOLSchDate datetime,
				PartNo varchar(50), CSLITQtyShipped int, CSLITShippedDate datetime, SoNo varchar(50), CustId int, SoPoNo varchar(30),
				SoPerson varchar(50), SoBkDate datetime, RegRep varchar(20), CustName varchar(65),
				CustNo varchar(20), CSTstatus varchar(15), CSTShipDate datetime, InvNo varchar(20),Qtr int,Yr int, Mth int, IsRMALineItem int, IsRMACredit VARCHAR(3),COOlLineItemNo int)
	
	declare @productID int, @cOOlQty int
	declare @cOOLUnitCost float
	declare @cOOLReqDate datetime, @cOOLSchDate datetime
	declare @partNo varchar(50)
	declare @cSLITQtyShipped int
	declare @cSLITShippedDate datetime
	declare @soNo varchar(50)
	declare @cust_id int
	declare @soPoNo varchar(30),@soPerson varchar(50)
	declare @soBkDate datetime
	declare @regRep varchar(20), @custName varchar(65),@custNo varchar(20), @cSTstatus varchar(15)
	declare @cSTShipDate datetime
	declare @invNo varchar(20)
	declare @year int
	declare @quarter int
	declare @isRMALineItem int
	declare @isdistributor	int


	declare @temp_year int
	declare @temp_q1_from varchar(20)
	declare @q1_from datetime,@q1_to datetime,@q2_from datetime,@q2_to datetime
	declare @q3_from datetime,@q3_to datetime,@q4_from datetime,@q4_to datetime,@q5_from datetime,@q5_to datetime
	declare @month int

	DECLARE @isRMACredit VARCHAR(3)
	declare @cOOLLineItemNo int

	SET @isRMALineItem = 2
	SET @isRMACredit = 'NO'		

	declare mainCursor cursor for
	SELECT
		Customer_Open_Order_LineItem.Product_ID, 
		Customer_Open_Order_LineItem.COOL_Qty, 
		Customer_Open_Order_LineItem.COOL_Unit_Cost, 
		Customer_Open_Order_LineItem.COOL_Requested_Date, 
		Customer_Open_Order_LineItem.COOL_Schedule_Date,
		Product.Part_Number,
		Customer_Shipping_Line_Item_Table.CSLIT_Qty_Shipped, 
		Customer_Shipping_Line_Item_Table.CSLIT_Shipped_Date,
		Sales_Order.SalesOrder_No, 
		Sales_Order.Customer_ID, 
		Sales_Order.SO_Customer_PO_Number, 
		Sales_Order.SO_Sales_Person, 
		Sales_Order.SO_Booking_Date, 
		Sales_Order.Regional_Rep,
		Customer.C_Name, Customer.C_Number,Customer.C_Is_Distributor,
		Customer_Shipping_Table.CST_Status, 
		Customer_Shipping_Table.CST_Ship_Date, 
		Customer_Shipping_Table.Inv_No,
		Customer_Open_Order_LineItem.COOL_Line_Item_No 

	FROM
	     ((((Customer_Open_Order_LineItem INNER JOIN  Customer_Shipping_Line_Item_Table ON
	        Customer_Open_Order_LineItem.LineItem_ID = Customer_Shipping_Line_Item_Table.LineItem_ID)
	     INNER JOIN Sales_Order ON Customer_Open_Order_LineItem.SalesOrder_ID = Sales_Order.SalesOrder_ID and Sales_Order.SO_Is_Sample = @order_type )
	     INNER JOIN Product ON Customer_Open_Order_LineItem.Product_ID = Product.Product_ID)
	     INNER JOIN Customer_Shipping_Table ON  Customer_Shipping_Line_Item_Table.CustomerShipping_ID = Customer_Shipping_Table.CustomerShipping_ID)
	     INNER JOIN Customer ON Sales_Order.Customer_ID = Customer.Customer_ID
	WHERE
	    Customer_Shipping_Table.CST_Status = 'SHIPPED'
	-- To Show Distributor Ship Info. @isRMALineItem = 2 Customer Ship,=1 Distributoor Ship,=0 rma
	union

	SELECT 
		DLIT.PRODUCT_ID,DLIT.DIST_REQ_QTY,DLIT.UNIT_COST,DLIT.DIST_REQ_DATE,null,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = DLIT.PRODUCT_ID) AS PRODUCT,
		DLIT.DIST_SHIP_QTY,DLIT.DIST_SHIP_DATE,DSO.DIST_SALESORDER_NO,DSO.CUSTOMER_ID,
		DSO.CUSTOMER_PO,(SELECT C_SALES_PERSON FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ),
		DSO.SALESORDER_DATE,null,
		(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ),
		(SELECT C_Number FROM CUSTOMER WHERE CUSTOMER_ID = DSO.CUSTOMER_ID ),null,null,
		DLIT.DIST_SHIP_DATE,'',DLIT.DIST_LINEITEM_NO
	FROM 
		 (DIST_SALES_ORDER DSO INNER JOIN DIST_LINE_ITEM_TABLE DLIT ON
	          DSO.DIST_SALESORDER_ID = DLIT.DIST_SALESORDER_ID)
	
		open mainCursor
		fetch next from mainCursor into 	
		@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
		@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo,@isdistributor, @cSTstatus, @cSTShipDate, @invNo, @cOOLLineItemNo 
		while @@fetch_status = 0
		begin
			if ( @isdistributor is null)
			begin
				set @isRMALineItem = 2
			end
			else if ( @isdistributor = 1 )
			begin
				set @isRMALineItem = 1
			end
			if @cStShipDate is not null
			begin
				
				set @month = DATEPART(m,@cStShipDate)
				set @year=DATEPART(yyyy,@cStShipDate)
				-- Modified by Prabhanjan (14 Apr 2004)
				--if @month >=2 and @month <=4
				if @month >=1 and @month <=3
				begin
					set @quarter = 1
					--set @month = 4
					set @month = 3
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo)
				end
				--else if  @month >=5 and @month <=7
				else if  @month >=4 and @month <=6
				begin
					set @quarter = 2
					--set @month = 7
					set @month = 6
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo )
					set @quarter =null
				end
				--else if @month >=8 and @month <=10
				else if @month >=7 and @month <=9
				begin
					set @quarter = 3
					--set @month = 10
					set @month = 9
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo )
					set @quarter =null
				end
				--else if @month =11 or  @month =12 
				else if @month >=10 or  @month <=12 
				begin
					set @quarter = 4
					--set @month = 1
					set @month = 12
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,12,@cStShipDate)))
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo )
					set @quarter =null
				end
				/*
				else if @month =1
				begin
					set @quarter = 4
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,-12,@cStShipDate)))
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month)
					set @quarter =null
				end
				*/
			end
			else
			begin
				--blind insert
				insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
				@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo)
			end
		
		set @productID=null
		set @cOOlQty=null
		set @cOOLUnitCost=null
		set @cOOLReqDate=null
		set @cOOLSchDate=null
		set @partNo=null
		set @cSLITQtyShipped=null
		set @cSLITShippedDate=null 
		set @soNo=null
		set @cust_id=null
		set @soPoNo=null
		set @soPerson=null
		set @soBkDate=null
		set @regRep=null
		set @custName=null
		set @custNo=null
		set @cSTstatus=null
		set @cSTShipDate=null
		set @invNo=null
		set @quarter=null
		set @year=null
		set @month=null
		set @cOOLLineItemNo=null
		set @isdistributor = null

		fetch next from mainCursor into 	
		@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
		@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo,@isdistributor, @cSTstatus,@cSTShipDate,@invNo,@cOOLLineItemNo 
		end
		close mainCursor
		deallocate mainCursor
		
		DECLARE @RMA_ID INT
		--DECLARE @TEMPCOST INT
		--SET @TEMPCOST = 0

		DECLARE @IS_CREDIT INT

		declare @lineItemCloseDate datetime
		declare @lineItemStatus varchar(10)

		SET @isRMALineItem = 0
		SET @IS_CREDIT = 1
				
		-- Modified by Prabhanjan(11 May 2004) to retreive Close_Date as @cSTShipDate
		if( @order_type <> 0 )
		begin
		DECLARE rmaCursor CURSOR FOR
		SELECT RMA_TABLE.RMA_ID, RMA_Number, Customer_ID, Customer_Name, RMA_Requested_Date, RMA_Table.Close_Date, Part_Number, Quantity, Unit_Price, PO_Number, Is_Credit,LineItem_Number,RMA_LINE_ITEM_TABLE.Close_Date , RMA_LINE_ITEM_TABLE.Status, RMA_LINE_ITEM_TABLE.Invoice_Number FROM RMA_TABLE, RMA_LINE_ITEM_TABLE WHERE RMA_TABLE.RMA_ID = RMA_LINE_ITEM_TABLE.RMA_ID AND RMA_LINE_ITEM_TABLE.Status <> 'CANCELLED'
		
		OPEN rmaCursor 
		FETCH NEXT FROM rmaCursor INTO @RMA_ID, @soNo, @cust_id, @custName, @cOOLReqDate, @cSTShipDate, @partNo, @cOOlQty, @cOOLUnitCost, @soPoNo, @IS_CREDIT, @cOOLLineItemNo , @lineItemCloseDate , @lineItemStatus, @invNo
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--SET @cOOLUnitCost = @TEMPCOST - @cOOLUnitCost
			SET @cSLITQtyShipped = @cOOLQty
			-- Commented by Prabhanjan (11 May 2004) - Now @cSTShipDate is taken as Close_Date of RMA, not Requested Date.
			-- SET @cSTShipDate = @cOOLReqDate

			SELECT @custNo = C_NUMBER FROM CUSTOMER WHERE CUSTOMER_ID = @cust_Id

			select @productID = Product.Product_ID from product,Process_Step where Part_Number = @partNo and product.processStep_id=Process_Step.ProcessStep_ID and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name = 'Finished Goods')
			
			-- ADDED BY PRABHANJAN (27 May 2004).TO INDICATE WHETHER CREDIT IS SELECTED OR NOT.
			IF (@IS_CREDIT	= 1)
			BEGIN
				SET @isRMACredit = 'NO'
			END
			ELSE IF (@IS_CREDIT = 0)
			BEGIN
				SET @isRMACredit = 'YES'
			END				
	
			if (@lineItemStatus = 'CLOSE')
			begin
				set @cSTShipDate = @lineItemCloseDate
			end


			--print 'before'
			if @cSTShipDate is not null
			begin
				/*
				print 'inn'
				set @temp_year =DATEPART(yyyy,@cStShipDate)
				set @temp_q1_from='02/01/'+convert(varchar,@temp_year)
				print @temp_q1_from
				set @q1_from=convert(datetime, @temp_q1_from)
				set @q1_to=dateadd(d,-1,dateadd(m,3,@q1_from))
				set @q2_from=dateadd(d,1, @q1_to)
				set @q2_to=dateadd(d,-1,dateadd(m,3,@q2_from))
				set @q3_from=dateadd(d,1, @q2_to)
				set @q3_to=dateadd(d,-1,dateadd(m,3,@q3_from))
				set @q4_from=dateadd(m,-12,dateadd(d,1, @q3_to))
				print @q4_from
				set @q4_to=dateadd(d,-1,dateadd(m,3,@q4_from))
				print @q4_to
				set @q5_from=dateadd(d,1, @q3_to)
				print @q5_from
				set @q5_to=dateadd(d,-1,dateadd(m,3,@q4_from))
				print @q5_to
				*/

				
				set @month = DATEPART(m,@cStShipDate)
				set @year=DATEPART(yyyy,@cStShipDate)
				-- Modified by Prabhanjan (14 Apr 2004)
				--if @month >=2 and @month <=4
				if @month >=1 and @month <=3
				begin
					set @quarter = 1
					--set @month = 4
					set @month = 3
				
					INSERT INTO @results VALUES (@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo, @cSTstatus, @cSTShipDate, @invNo, @quarter, @year, @month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo)

				end
				--else if  @month >=5 and @month <=7
				else if  @month >=4 and @month <=6
				begin
					set @quarter = 2
					--set @month = 7
					set @month = 6
					INSERT INTO @results VALUES (@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo, @cSTstatus, @cSTShipDate, @invNo, @quarter, @year, @month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo)
					set @quarter =null
				end
				--else if @month >=8 and @month <=10
				else if @month >=7 and @month <=9
				begin
					set @quarter = 3
					--set @month = 10
					set @month = 9
					INSERT INTO @results VALUES (@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo, @cSTstatus, @cSTShipDate, @invNo, @quarter, @year, @month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo)
					set @quarter =null
				end
				--else if @month =11 or  @month =12 
				else if @month >=10 or  @month <=12 
				begin
					set @quarter = 4
					--set @month = 1
					set @month = 12
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,12,@cStShipDate)))
					INSERT INTO @results VALUES (@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
					@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo, @cSTstatus, @cSTShipDate, @invNo, @quarter, @year, @month, @isRMALineItem, @isRMACredit, @cOOLLineItemNo)
					set @quarter =null
				end
				/*
				else if @month =1
				begin
					set @quarter = 4
					--set @year = convert(int,DATEPART(yyyy,dateadd(m,-12,@cStShipDate)))
					insert into @results values(@productID, @cOOlQty,@cOOLUnitCost,@cOOLReqDate, @cOOLSchDate,@partNo,@cSLITQtyShipped,@cSLITShippedDate, 
					@soNo,@cust_id, @soPoNo,@soPerson,@soBkDate,@regRep, @custName,@custNo, @cSTstatus,@cSTShipDate,@invNo,@quarter,@year,@month)
					set @quarter =null
				end
				*/
			end
			--Commented by Prabhanjan (11 May 2004)
			-- To insert only those RMA which has Close Date available.
			/*
			else
			begin
				--blind insert
				INSERT INTO @results VALUES (@productID, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, @cOOLSchDate, @partNo, @cSLITQtyShipped, @cSLITShippedDate, 
				@soNo, @cust_id, @soPoNo, @soPerson, @soBkDate, @regRep, @custName, @custNo, @cSTstatus, @cSTShipDate, @invNo, @quarter, @year, @month, @isRMALineItem)
			end
			*/
			/*
			INSERT INTO @results VALUES (NULL, @cOOlQty, @cOOLUnitCost, @cOOLReqDate, NULL, @partNo, @cSLITQtyShipped, NULL, 
			@soNo, @cust_id, NULL, NULL, NULL, NULL, @custName, NULL, NULL, @cSTShipDate, NULL, NULL, NULL, NULL)
			*/
			set @productID=null
			set @cOOlQty=null
			set @cOOLUnitCost=null
			set @cOOLReqDate=null
			set @cOOLSchDate=null
			set @partNo=null
			set @cSLITQtyShipped=null
			set @cSLITShippedDate=null 
			set @soNo=null
			set @cust_id=null
			set @soPoNo=null
			set @soPerson=null
			set @soBkDate=null
			set @regRep=null
			set @custName=null
			set @custNo=null
			set @cSTstatus=null
			set @cSTShipDate=null
			set @invNo=null
			set @quarter=null
			set @year=null
			set @month=null
			set @cOOLLineItemNo=null

			set @lineItemCloseDate = null
			set @lineItemStatus =  null

			FETCH NEXT FROM rmaCursor INTO @RMA_ID, @soNo, @cust_id, @custName, @cOOLReqDate, @cSTShipDate, @partNo, @cOOlQty, @cOOLUnitCost, @soPoNo, @IS_CREDIT, @cOOLLineItemNo , @lineItemCloseDate , @lineItemStatus, @invNo

		END
		CLOSE rmaCursor
		DEALLOCATE rmaCursor
		end
		
	select * from @results order by Yr, Qtr
	
end